﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VLine.InputDto._0416;
using HCQ2_Model.VLine.InputDto._20220219;
using HCQ2_Model.VLine.OutputDto._20220219;
using HCQ2_Model.VLine.Params;
using HCQ2_Model.VLine.Result;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDKeywordDAL: IT_VRDDKeywordDAL
    {
        /// <summary>
        ///  获取好友申请列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<SKeywordResult> GetKeywordList(KeywordParam param, out int total)
        {
            sb?.Clear();
            sb.Append($@"SELECT id,keyword,degree,pro_type,create_name,
            CONVERT(varchar(100),create_date,120)AS create_date,update_name,
            CONVERT(varchar(100),update_date,120)AS update_date,
            ROW_NUMBER() OVER(ORDER BY create_date DESC) AS num 
            FROM T_VRDDKeyword WHERE 1=1 ");
            if (param.vrType > 0)
                sb.Append($" AND pro_type={param.vrType} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND keyword LIKE '%{param.keyword}%' ");
            sb.Append($")word ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar($"SELECT COUNT(0) FROM ( {sb.ToString()}"));
            return SqlHelper.ExecuteList<SKeywordResult>($"SELECT TOP {param.rows} * FROM ( {sb.ToString()} WHERE word.num>{(param.page - 1) * param.rows}");
        }

        //***************************2.0 消息推送*******************************
        /// <summary>
        ///  获取推送消息列表
        /// </summary>
        /// <param name="messType">消息类别</param>
        /// <param name="keyword">关键字</param>
        /// <param name="total">返回条数</param>
        /// <returns></returns>
        public List<MessListResult> GetMessTableList(MessTableParam param, out int total)
        {
            sb?.Clear();
            sb.Append($@"SELECT ROW_NUMBER() OVER(ORDER BY create_date DESC)AS num,mess_id,
            CONVERT(varchar(100),update_date,120)AS update_date,send_name,send_status,send_num
            ,mess_type,store_name,mess_note,CONVERT(varchar(100),create_date,120)AS create_date 
            FROM T_VRDDSendMess  WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.messType))
                sb.Append($" AND mess_type='{param.messType}' ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (mess_note LIKE '%{param.keyword}%' OR send_name LIKE '%{param.keyword}%' OR store_name LIKE '%{param.keyword}%') ");
            sb.Append(")mess ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar($"SELECT COUNT(0) FROM ( {sb.ToString()}"));
            return SqlHelper.ExecuteList<MessListResult>($"SELECT TOP {param.rows} * FROM ( {sb.ToString()} WHERE mess.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取推送用用户列表
        /// </summary>
        /// <param name="messType">消息类别</param>
        /// <param name="messNote">消息内容</param>
        /// <param name="keyword">接收人、手机号</param>
        /// <param name="messId">消息id</param>
        /// <param name="total">返回条数</param>
        /// <returns></returns>
        public List<MessUserListResult> GetMessUserTableList(MessUserTableParam param, out int total)
        {
            sb?.Clear();
            sb.Append($@"SELECT ROW_NUMBER() OVER(ORDER BY s.create_date DESC)AS num,s.*,m.mess_type,m.store_name,
            m.mess_note FROM (SELECT mess_id,mess_type,store_name,mess_note FROM T_VRDDSendMess WHERE 1=1");
            if (!string.IsNullOrEmpty(param.messType))
                sb.Append($" AND mess_type='{param.messType}' ");
            if(!string.IsNullOrEmpty(param.messNote))
                sb.Append($" AND mess_note LIKE '%{param.messNote}%' ");
            if (param.messId > 0)
                sb.Append($" AND mess_id={param.messId} ");
            sb.Append($@")m INNER JOIN(SELECT mess_id,CONVERT(varchar(100),create_date,120)AS create_date,
            CONVERT(varchar(100),push_date,120)AS push_date,push_status,accept_name,
            accept_phone,accept_status,CONVERT(varchar(100),accept_date,120)AS accept_date
            FROM T_VRDDSendUser u WHERE 1=1 ");
            if (param.messId > 0)
                sb.Append($" AND mess_id={param.messId} ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($@" AND (accept_name LIKE '%{param.keyword}%' OR accept_phone LIKE '%{param.keyword}%') ");
            sb.Append(")s ON m.mess_id=s.mess_id)mess ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar($"SELECT COUNT(0) FROM ( {sb.ToString()}"));
            return SqlHelper.ExecuteList<MessUserListResult>($"SELECT TOP {param.rows} * FROM ( {sb.ToString()} WHERE mess.num>{(param.page - 1) * param.rows}");
        }

        //*************************** 接口消息推送*******************************
        /// <summary>
        ///  获取推送消息列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<PushMsgOutput> GetPushMsgList(InputPushMsgParam param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM (
            SELECT me.*,folder.*,mu.*,ROW_NUMBER() OVER(ORDER BY mu.createTime DESC)AS number FROM 
            (SELECT mess_id AS msgID,mess_type AS msgType,store_id AS storeID,mess_note AS msgContent,
            send_id AS SenderID,send_name AS SenderName,unreadNum=(SELECT COUNT(0) FROM dbo.T_VRDDSendUser WHERE 
            mess_id=t.mess_id AND accept_status=-1),SenderPhone=(SELECT user_phone FROM dbo.T_User
            WHERE user_id=t.send_id) FROM dbo.T_VRDDSendMess t WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (mess_note LIKE '%{param.keyword}%' OR store_name LIKE '%{param.keyword}%') ");
            sb.Append($@")me INNER JOIN 
            (SELECT folder_id,folder_name AS storeName,folder_url AS storeImage,folder_path AS
            folderPath FROM dbo.T_DocumentFolder)folder ON me.storeID=folder.folder_id INNER JOIN 
            (SELECT m.* FROM (SELECT ROW_NUMBER() OVER(PARTITION BY store_id ORDER BY create_date)AS num,
            id AS msgUserID,mess_id,CONVERT(varchar(100),create_date,120)AS createTime,
            accept_status AS msgStatus FROM dbo.T_VRDDSendUser WHERE accept_id={user_id}) m WHERE m.num=1)mu 
            ON me.msgID=mu.mess_id)mess WHERE mess.number>{(param.page - 1) * param.rows};");
            return SqlHelper.ExecuteList<PushMsgOutput>(sb.ToString());
        }
        /// <summary>
        ///  获取店铺推送消息
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<StorePushOutput> GetStorePushMsg(StorePushInput param, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM (
            SELECT me.*,folder.*,mu.*,ROW_NUMBER() OVER(ORDER BY mu.createTime DESC)AS number FROM 
            (SELECT mess_id AS msgID,mess_type AS msgType,store_id AS storeID,mess_note AS msgContent,
            send_id AS SenderID,send_name AS SenderName,unreadNum=(SELECT COUNT(0) FROM dbo.T_VRDDSendUser WHERE 
            mess_id=t.mess_id AND accept_status=-1 AND accept_id={user_id}),SenderPhone=(SELECT user_phone FROM dbo.T_User
            WHERE user_id=t.send_id) FROM dbo.T_VRDDSendMess t WHERE t.store_id={param.storeID})me INNER JOIN 
            (SELECT folder_id,folder_name AS storeName,folder_url AS storeImage,folder_path AS
            folderPath FROM dbo.T_DocumentFolder)folder ON me.storeID=folder.folder_id INNER JOIN 
            (SELECT id AS msgUserID,mess_id,CONVERT(varchar(100),create_date,120)AS createTime,
            accept_status AS msgStatus FROM dbo.T_VRDDSendUser WHERE accept_id={user_id} AND store_id={param.storeID})mu 
            ON me.msgID=mu.mess_id)mess WHERE mess.number>{(param.page-1)*param.rows};");
            return SqlHelper.ExecuteList<StorePushOutput>(sb.ToString());
        }
    }
}